package com.situ.company.score.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.company.score.model.ScoreModel;
import com.situ.company.util.JDBCUtil;

public class ScoreDao {

	/**
	 * 添加
	 * 
	 * @param model
	 * @return
	 */
	public Integer insert(ScoreModel model) {
		String sql = "insert into score(code_emp,code_pro,score)values(?,?,?)";
		return JDBCUtil.update(sql, model.getCodeEmp(), model.getCodePro(), model.getScore());
	}

	/**
	 * 删除
	 * 
	 * @param id
	 * @return
	 */
	public Integer delete(Integer id) {
		String sql = "delete from score where id=?";
		return JDBCUtil.update(sql, id);
	}

	/**
	 * sql语句拼接
	 * 
	 * @param sql
	 * @param model
	 * @return
	 */
	private List<Object> appendWhere(StringBuffer sql, ScoreModel model) {
		sql.append(" where 1=1 ");
		List<Object> objs = new ArrayList<>();
		Integer id = model.getId();
		if (id != null) {
			sql.append(" and id=? ");
			objs.add(id);
		}
		String codeEmp = model.getCodeEmp();
		if (codeEmp != null && !codeEmp.trim().isEmpty()) {
			sql.append(" and code_emp like ? ");
			objs.add(codeEmp);
		}
		String codePro = model.getCodePro();
		if (codePro != null && !codePro.trim().isEmpty()) {
			sql.append(" and code_pro like ? ");
			objs.add(codePro);
		}
		String score = model.getScore();
		if (score != null && !score.trim().isEmpty()) {
			sql.append(" and score like ? ");
			objs.add(score);
		}
		String order = model.getOrderby();
		if (order != null && !order.trim().isEmpty()) {
			sql.append(" order by ").append(order);
		}
		if (model.isPageOn()) {
			sql.append(" limit ?,? ");
			objs.add(model.getRowStart());
			objs.add(model.getRowCount());
		}
		return objs;
	}

	/**
	 * 修改
	 * 
	 * @param model
	 * @return
	 */
	public Integer update(ScoreModel model) {
		StringBuffer sql = new StringBuffer("update score ");
		List<Object> vals = appendSet(sql, model);
		return JDBCUtil.update(sql.toString(), vals);
	}

	/**
	 * 修改拼接
	 * 
	 * @param sql
	 * @param model
	 * @return
	 */
	private List<Object> appendSet(StringBuffer sql, ScoreModel model) {
		sql.append(" set id=id");
		List<Object> objs = new ArrayList<>();
		String codeEmp = model.getCodeEmp();
		if (codeEmp != null) {
			sql.append(" ,code_emp = ?");
			objs.add(codeEmp);
		}
		String codePro = model.getCodePro();
		if (codePro != null) {
			sql.append(" ,code_pro = ? ");
			objs.add(codePro);
		}
		String score = model.getScore();
		if (score != null) {
			sql.append(" ,score = ? ");
			objs.add(score);
		}
		sql.append(" where id=?");
		objs.add(model.getId());
		return objs;
	}

	/**
	 * 查询记录
	 * 
	 * @param model
	 * @return
	 */
	public List<ScoreModel> selectList(ScoreModel model) {
		// select code_emp,code_pro,score,(select p.name from project p where
		// p.code = score.code_pro)p_name,
		// (select e.name from employee e where e.code = score.code_emp)e_name
		// from score;
		StringBuffer sql = new StringBuffer("select id,code_emp,code_pro,score,");
		sql.append("(select p.name from project p where p.code = score.code_pro)p_name,")
				.append("(select e.name from employee e where e.code = score.code_emp)e_name ").append("from score");
		List<Object> vals = appendWhere(sql, model);
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement ps = null;
		List<ScoreModel> list = new ArrayList<>();
		try {
			conn = JDBCUtil.getConnertion();
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < vals.size(); i++)
				ps.setObject(i + 1, vals.get(i));
			rs = ps.executeQuery();
			while (rs.next()) {
				ScoreModel mdb = new ScoreModel();
				mdb.setId(rs.getInt("id"));
				mdb.setCodeEmp(rs.getString("code_emp"));
				mdb.setCodePro(rs.getString("code_pro"));
				mdb.setScore(rs.getString("score"));
				mdb.seteName("e_name");
				mdb.setpName("p_name");
				list.add(mdb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
		}
		return list;
	}

	/**
	 * @param model
	 * @return 查询记录条数
	 */
	public int selectCount(ScoreModel model) {
		StringBuffer sql = new StringBuffer("select count(id) from score");
		List<Object> vals = appendWhere(sql, model);
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JDBCUtil.getConnertion();
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < vals.size(); i++)
				ps.setObject(i + 1, vals.get(i));
			rs = ps.executeQuery();
			if (rs.next())
				return rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
		}
		return 0;
	}

}
